package com.dingding.manager.modules.order.sql;

import java.util.List;

import org.apache.commons.collections.CollectionUtils;

import com.dingding.manager.core.util.SqlUtil;
import com.dingding.manager.core.util.ToolUtil;
import com.dingding.manager.modules.order.enums.OrderStatus;

/**
 * 
 * @ClassName: OrderSql 
 * @Description: 订单相关的sql
 * @author hufan
 * @date 2016年12月7日 下午5:38:35 
 *
 */
public class OrderSql {

	/**
	 * 
	 */
	public static final String QUERY_USERSPECIALORDERT_BY_GRABUSERID = "";
	
	/**
	 * 根据订单id查询出租车临时订单
	 */
	public static final String QUERY_USERTAXIORDERT_BY_OID = "SELECT o.`taxiOrdertId` as oid, o.`alipayMoney`,o.`cancelDesc`,o.`cancelTime`,o.`cancelType`,o.`cancelUserId`,o.`createTime`,o.`distance`,o.`endAddress`,o.`endLatitude`,o.`endLongitude`,o.`endTime`,o.`grabOrderTime`,o.`grabOrderUserId`,o.`isComment`,o.`message`,o.`number`,o.`orderStatus`,o.`orderType`,o.`payDistance`,o.`payMoney`,o.`payTime`,o.`payType`,o.`placeOrderCity`,o.`placeOrderUserId`,o.`setOutTime`,o.`startAddress`,o.`startLatitude`,o.`startLongitude`,o.`totalMoney`,o.`transactionType` FROM `usertaxiordert` o WHERE o.`taxiOrdertId` = ?";
	
	/**
	 *  根据订单id查询出租车订单
	 */
	public static final String QUERY_USERORDER_BY_OID = "SELECT o.`oid`, o.`alipayMoney`,o.`cancelDesc`,o.`cancelNumber`,o.`cancelTime`,o.`cancelType`,o.`cancelUserId`,o.`couponMoney`,o.`createTime`, o.`distance`,o.`driverRcvIntegral`,o.`endAddress`,o.`endLatitude`,o.`endLongitude`,o.`endTime`,o.`grabOrderTime`,o.`grabOrderUserId`,o.`isCarShare`, o.`isChildren`,o.`isComment`,o.`isLongDistance`,o.`marker`,o.`message`,o.`number`,o.`orderStatus`,o.`orderType`,o.`parOrderId`,o.`payCity` ,o.`payDistance`,o.`payMoney`,o.`payTime`,o.`payType`,o.`placeOrderUserId`,o.`psgRcvIntegral`,o.`publishType`,o.`redBag`,o.`setOutCondition`,o.`setOutTime`, o.`startAddress`,o.`startLatitude`,o.`startLongitude`,o.`totalMoney`,o.`transactionType`,o.`ucId`,o.`startFee`, o.`minimums`, o.`durationFee`, o.`longDistance`, o.`longFee`, o.`nightDistance`, o.`nightFee` FROM `userorder` o WHERE o.`oid` = ?";
	
	/**
	 *  根据订单id查询专车临时订单信息
	 */
	public static final String QUERY_USERSPECIALORDERT_BY_OID = "SELECT o.`specialOrderId` as oid, o.`aboardLatitude`, o.`aboardLongitude`, o.`aboardTime`,o.`alipayMoney`,o.`appointTime`,o.`cancelDesc`,o.`cancelType`,o.`cancelTime`,o.`createTime`, o.`debusLatitude` ,o.`debusLongitude`,o.`depId`,o.`distance`,o.`duration`,o.`endAddress`,o.`endLatitude`,o.`endLongitude` ,o.`endTime`,o.`estimateDistance`,o.`estimateDuration`,o.`estimateMoney`,o.`grabOrderUserId`,o.`operatorId`,o.`orderStatus`, o.`payMoney`,o.`payTime`,o.`payType`,o.`placeOrderCity`,o.`placeOrderCityCode`,o.`placeOrderUserId`,o.`publishType`,o.`routeId`,o.`setOutTime` ,o.`setOutType`,o.`specialOrderId`,o.`startAddress`,o.`startLatitude`,o.`startLongitude`,o.`totalMoney`,o.`vehicleLevelId`,o.`startFee`, o.`minimums`, o.`durationFee`, o.`longDistance`, o.`longFee`, o.`nightDistance`, o.`nightFee` FROM `userspecialordert` o WHERE o.`specialOrderId` = ?";
	
	/**
	 *  根据订单id查询专车订单信息
	 */
	public static final String QUERY_USERSPECIALORDER_BY_OID = "SELECT o.`specialOrderId` as oid, o.`aboardLatitude`, o.`aboardLongitude`, o.`aboardTime`,o.`alipayMoney`,o.`appointTime`,o.`cancelDesc`,o.`cancelType`,o.`cancelTime`,o.`createTime`,o.`debusLatitude` ,o.`debusLongitude`,o.`depId`,o.`distance`,o.`duration`,o.`endAddress`,o.`endLatitude`,o.`endLongitude` ,o.`endTime`,o.`estimateDistance`,o.`estimateDuration`,o.`estimateMoney`,o.`grabOrderUserId`,o.`operatorId`,o.`orderStatus`, o.`payMoney`,o.`payTime`,o.`payType`,o.`placeOrderCity`,o.`placeOrderCityCode`,o.`placeOrderUserId`,o.`publishType`,o.`routeId`,o.`setOutTime` ,o.`setOutType`,o.`startAddress`,o.`startLatitude`,o.`startLongitude`,o.`totalMoney`,o.`vehicleLevelId`,o.`startFee`, o.`minimums`, o.`durationFee`, o.`longDistance`, o.`longFee`, o.`nightDistance`, o.`nightFee` FROM `userspecialorder` o WHERE o.`specialOrderId` = ?";
	
	/**
	 * 根据订单id查询评论信息
	 */
	public static final String QUERY_ORDERCOMMENT_BY_OID = "SELECT c.`spcId`, c.`specialOrderId`, c.`evaluateContent`, c.`evaluateDate`, c.`evaluateLabel`, c.`evaluateLevel`, c.`isDispay` FROM `specialordercomment` c WHERE c.`specialOrderId` = ?";
	
	
	/**
	 * 根据司机id查询公司基本信息
	 */
	public static final String DRIVER_BASIC_INFO = "SELECT ui.`userId`, ui.`telPhone`, uai.`trueName`, o.`organizationName`, o.`contactsNumber` , o.`cityName`, p.`provice_name` AS provinceName FROM specialuserinfo ui LEFT JOIN dd_special_user_auth_info uai ON ui.userId = uai.specialUserId LEFT JOIN dd_organization o ON uai.organizationId = o.organizationId LEFT JOIN `dd_sys_provice_city` pc ON o.`cityId`= pc.`sysCityId` LEFT JOIN `dd_sys_province` p ON pc.`proviceId`=p.`provice_id` WHERE ui.`userId` = ?";
	
	
	/**
	 * 
	 * @Title: getTaxiOrdertQuerySql 
	 * @Description: 获取taxi临时表的sql
	 * @param @param driverIds
	 * @param @param orderStatus
	 * @param @param startTime
	 * @param @param endDate
	 * @param nameOrPhone 
	 * @param @return  参数说明 
	 * @return String    返回类型 
	 * @throws
	 */
	public static String getTaxiOrdertQuerySql(List<String> driverIds, OrderStatus orderStatus,
			String startTime, String endDate, String address) {
		
		StringBuffer sql = new StringBuffer("SELECT o.`taxiOrdertId` as oid, o.`alipayMoney`,o.`cancelDesc`,o.`cancelTime`,o.`cancelType`,o.`cancelUserId`,o.`createTime`,o.`distance`,o.`endAddress`,o.`endLatitude`,o.`endLongitude`,o.`endTime`,o.`grabOrderTime`,o.`grabOrderUserId`,o.`isComment`,o.`message`,o.`number`,o.`orderStatus`,o.`orderType`,o.`payDistance`,o.`payMoney`,o.`payTime`,o.`payType`,o.`placeOrderCity`,o.`placeOrderUserId`,o.`setOutTime`,o.`startAddress`,o.`startLatitude`,o.`startLongitude`,o.`totalMoney`,o.`transactionType` FROM `usertaxiordert` o WHERE 1=1 ");
		//根据出发地或者目的地模糊查询
		if(ToolUtil.isNotEmpty(address)){
			sql.append(" AND ( o.`startAddress` LIKE '%"+address+"%' OR o.`endAddress` LIKE '%"+address+"%')");
		}
		if (ToolUtil.isNotEmpty(startTime)) {
			sql.append(" AND date_format(o.`createTime`, '%Y-%m-%d') >= '"+startTime+"'");
		}
		if (ToolUtil.isNotEmpty(endDate)) {
			sql.append(" AND date_format(o.`createTime`, '%Y-%m-%d') <= '"+endDate+"'");
		}
		if(orderStatus != OrderStatus.ALL) {
			//1：待抢单  2：服务中 3：待支付 4：已完成 5：已取消
			if(orderStatus == OrderStatus.INSERVICE) {
				sql.append(" AND o.`orderStatus` = 1 OR o.`orderStatus` = 2");
			}else if(orderStatus == OrderStatus.WAITPAY) {
				sql.append(" AND o.`orderStatus` = 3");
			}else if(orderStatus == OrderStatus.COMPLETED) {
				sql.append(" AND o.`orderStatus` = 4");
			}else if(orderStatus == OrderStatus.CANCELED) {
				sql.append(" AND o.`orderStatus` = 5");
			}
		}
		if(CollectionUtils.isNotEmpty(driverIds)) {
			sql.append(" AND o.`grabOrderUserId` in (" + SqlUtil.parse(driverIds) + ")");
		}
		sql.append("ORDER BY o.`createTime` DESC");
		return sql.toString();
	}
	
	/**
	 * 
	 * @Title: getUserOrderQuerySql 
	 * @Description: 获取出租车订单sql
	 * @param @param driverIds
	 * @param @param orderStatus
	 * @param @param startTime
	 * @param @param endDate
	 * @param nameOrPhone 
	 * @param @return  参数说明 
	 * @return String    返回类型 
	 * @throws
	 */
	public static String getUserOrderQuerySql(List<String> driverIds, OrderStatus orderStatus,
			String startTime, String endDate, String address) {
		
		StringBuffer sql = new StringBuffer(" SELECT o.`oid`, o.`alipayMoney`,o.`cancelDesc`,o.`cancelNumber`,o.`cancelTime`,o.`cancelType`,o.`cancelUserId`,o.`couponMoney`,o.`createTime`, o.`distance`,o.`driverRcvIntegral`,o.`endAddress`,o.`endLatitude`,o.`endLongitude`,o.`endTime`,o.`grabOrderTime`,o.`grabOrderUserId`,o.`isCarShare`, o.`isChildren`,o.`isComment`,o.`isLongDistance`,o.`marker`,o.`message`,o.`number`,o.`orderStatus`,o.`orderType`,o.`parOrderId`,o.`payCity` ,o.`payDistance`,o.`payMoney`,o.`payTime`,o.`payType`,o.`placeOrderUserId`,o.`psgRcvIntegral`,o.`publishType`,o.`redBag`,o.`setOutCondition`,o.`setOutTime`, o.`startAddress`,o.`startLatitude`,o.`startLongitude`,o.`totalMoney`,o.`transactionType`,o.`ucId`,o.`startFee`, o.`minimums`, o.`durationFee`, o.`longDistance`, o.`longFee`, o.`nightDistance`, o.`nightFee` FROM `userorder` o WHERE 1=1");
		//根据出发地或者目的地模糊查询
		if(ToolUtil.isNotEmpty(address)){
			sql.append(" AND ( o.`startAddress` LIKE '%"+address+"%' OR o.`endAddress` LIKE '%"+address+"%') ");
		}
		if (ToolUtil.isNotEmpty(startTime)) {
			sql.append(" AND date_format(o.`createTime`, '%Y-%m-%d') >= '"+startTime+"'");
		}
		if (ToolUtil.isNotEmpty(endDate)) {
			sql.append(" AND date_format(o.`createTime`, '%Y-%m-%d') <= '"+endDate+"'");
		}
		if(orderStatus != OrderStatus.ALL) {
			//1：待抢单  2：服务中 3：待支付 4：已完成 5：已取消
			if(orderStatus == OrderStatus.INSERVICE) {
				sql.append(" AND o.`orderStatus` = 1 OR o.`orderStatus` = 2");
			}else if(orderStatus == OrderStatus.WAITPAY) {
				sql.append(" AND o.`orderStatus` = 3");
			}else if(orderStatus == OrderStatus.COMPLETED) {
				sql.append(" AND o.`orderStatus` = 4");
			}else if(orderStatus == OrderStatus.CANCELED) {
				sql.append(" AND o.`orderStatus` = 5");
			}
		}
		if(CollectionUtils.isNotEmpty(driverIds)) {
			sql.append(" AND o.`grabOrderUserId` in (" + SqlUtil.parse(driverIds) + ")");
		}
		
		sql.append(" ORDER BY o.`createTime` DESC");
		return sql.toString();
	}
	
	/**
	 * 
	 * @Title: getSpecialOrdertQuerySql 
	 * @Description: 获取专车临时表的sql
	 * @param @param driverIds
	 * @param @param orderStatus
	 * @param @param startTime
	 * @param @param endDate
	 * @param nameOrPhone
	 * @param @return  参数说明 
	 * @return String    返回类型 
	 * @throws
	 */
	public static String getSpecialOrdertQuerySql(List<String> driverIds, OrderStatus orderStatus,
			String startTime, String endDate, String address) {
		
		StringBuffer sql = new StringBuffer("SELECT o.`specialOrderId` as oid, o.`aboardLatitude`, o.`aboardLongitude`, o.`aboardTime`,o.`alipayMoney`,o.`appointTime`,o.`cancelDesc`,o.`cancelType`,o.`cancelTime`,o.`createTime`, o.`debusLatitude` ,o.`debusLongitude`,o.`depId`,o.`distance`,o.`duration`,o.`endAddress`,o.`endLatitude`,o.`endLongitude` ,o.`endTime`,o.`estimateDistance`,o.`estimateDuration`,o.`estimateMoney`,o.`grabOrderUserId`,o.`operatorId`,o.`orderStatus`, o.`payMoney`,o.`payTime`,o.`payType`,o.`placeOrderCity`,o.`placeOrderCityCode`,o.`placeOrderUserId`,o.`publishType`,o.`routeId`,o.`setOutTime` ,o.`setOutType`,o.`startAddress`,o.`startLatitude`,o.`startLongitude`,o.`totalMoney`,o.`vehicleLevelId`,o.`startFee`, o.`minimums`, o.`durationFee`, o.`longDistance`, o.`longFee`, o.`nightDistance`, o.`nightFee` FROM `userspecialordert` o WHERE 1=1 ");
		//根据出发地或者目的地模糊查询
		if(ToolUtil.isNotEmpty(address)){
			sql.append(" AND ( o.`startAddress` LIKE '%"+address+"%' OR o.`endAddress` LIKE '%"+address+"%') ");
		}
		if (ToolUtil.isNotEmpty(startTime)) {
			sql.append(" AND date_format(o.`createTime`, '%Y-%m-%d') >= '"+startTime+"'");
		}
		if (ToolUtil.isNotEmpty(endDate)) {
			sql.append(" AND date_format(o.`createTime`, '%Y-%m-%d') <= '"+endDate+"'");
		}
		if(CollectionUtils.isNotEmpty(driverIds)) {
			sql.append(" AND o.`grabOrderUserId` in (" + SqlUtil.parse(driverIds) + ")");
		}
		
		if(orderStatus != OrderStatus.ALL) {
			//1:待指派  2:待上车(已指派)  3:已上车  4:待支付(已到达)  5:已完成  6:已取消
			if(orderStatus == OrderStatus.INSERVICE) {
				sql.append(" AND o.`orderStatus` = 1 OR o.`orderStatus` = 2 OR o.`orderStatus` = 3");
			}else if(orderStatus == OrderStatus.WAITPAY) {
				sql.append(" AND o.`orderStatus` = 4");
			}else if(orderStatus == OrderStatus.COMPLETED) {
				sql.append(" AND o.`orderStatus` = 5");
			}else if(orderStatus == OrderStatus.CANCELED) {
				sql.append(" AND o.`orderStatus` = 6");
			}
		}
		
		sql.append(" ORDER BY o.`createTime` DESC");
		return sql.toString();
	}
	
	/**
	 * 
	 * @Title: getSpecialOrderQuerySql 
	 * @Description: 获取专车订单表
	 * @param @param driverIds
	 * @param @param orderStatus
	 * @param @param startTime
	 * @param @param endDate
	 * @param nameOrPhone 
	 * @param @return  参数说明 
	 * @return String    返回类型 
	 * @throws
	 */
	public static String getSpecialOrderQuerySql(List<String> driverIds, OrderStatus orderStatus,
			String startTime, String endDate, String address) {
		
		StringBuffer sql = new StringBuffer("SELECT o.`specialOrderId` as oid, o.`aboardLatitude`, o.`aboardLongitude`, o.`aboardTime`,o.`alipayMoney`,o.`appointTime`,o.`cancelDesc`,o.`cancelType`,o.`cancelTime`,o.`createTime`,o.`debusLatitude` ,o.`debusLongitude`,o.`depId`,o.`distance`,o.`duration`,o.`endAddress`,o.`endLatitude`,o.`endLongitude` ,o.`endTime`,o.`estimateDistance`,o.`estimateDuration`,o.`estimateMoney`,o.`grabOrderUserId`,o.`operatorId`,o.`orderStatus`, o.`payMoney`,o.`payTime`,o.`payType`,o.`placeOrderCity`,o.`placeOrderCityCode`,o.`placeOrderUserId`,o.`publishType`,o.`routeId`,o.`setOutTime` ,o.`setOutType`,o.`startAddress`,o.`startLatitude`,o.`startLongitude`,o.`totalMoney`,o.`vehicleLevelId`,o.`startFee`, o.`minimums`, o.`durationFee`, o.`longDistance`, o.`longFee`, o.`nightDistance`, o.`nightFee` FROM `userspecialorder` o WHERE 1=1 ");
		//根据出发地或者目的地模糊查询
		if(ToolUtil.isNotEmpty(address)){
			sql.append(" AND ( o.`startAddress` LIKE '%"+address+"%' OR o.`endAddress` LIKE '%"+address+"%') ");
		}
		if (ToolUtil.isNotEmpty(startTime)) {
			sql.append(" AND date_format(o.`createTime`, '%Y-%m-%d') >= '"+startTime+"'");
		}
		if (ToolUtil.isNotEmpty(endDate)) {
			sql.append(" AND date_format(o.`createTime`, '%Y-%m-%d') <= '"+endDate+"'");
		}
		if(CollectionUtils.isNotEmpty(driverIds)) {
			sql.append(" AND o.`grabOrderUserId` in (" + SqlUtil.parse(driverIds) + ")");
		}
		if(orderStatus != OrderStatus.ALL) {
			//1:待指派  2:待上车(已指派)  3:已上车  4:待支付(已到达)  5:已完成  6:已取消
			if(orderStatus == OrderStatus.INSERVICE) {
				sql.append(" AND o.`orderStatus` = 1 OR o.`orderStatus` = 2 OR o.`orderStatus` = 3");
			}else if(orderStatus == OrderStatus.WAITPAY) {
				sql.append(" AND o.`orderStatus` = 4");
			}else if(orderStatus == OrderStatus.COMPLETED) {
				sql.append(" AND o.`orderStatus` = 5");
			}else if(orderStatus == OrderStatus.CANCELED) {
				sql.append(" AND o.`orderStatus` = 6");
			}
		}
		
		sql.append(" ORDER BY o.`createTime` DESC");
		return sql.toString();
	}
	
	public static String getOrderQuerySql() {
		String sql = "SELECT c.* FROM (SELECT sr.`sid`, sr.`supply_id` AS scid, DATE_FORMAT(sr.`create_time`, '%Y%m%d') AS create_time FROM shelves_supply_record sr UNION ALL SELECT cr.`sid`, cr.`change_id` AS scid, DATE_FORMAT(cr.`create_time`, '%Y%m%d') AS create_time FROM shelves_change_record cr) c GROUP BY c.`sid`, c.`create_time` ORDER BY c.`create_time` DESC";
	
		return sql; 
	}
	
	public static void main(String[] args) {
		//System.out.println("order_other_info_by_oid".toUpperCase());
		Object o = null;
		String s = (String)o;
		System.out.println((String)o);
		System.out.println(s == null);
	}
}
